import pandas as pd
import numpy as np
import os

def process_excel_file(file_path):
    df = pd.read_excel(file_path)

    # Assuming the first column is the classification label
    label_column = df.columns[0]
    
    # Create an empty DataFrame to store the reordered data
    df_reordered = pd.DataFrame(columns=df.columns)
    df_moved = pd.DataFrame(columns=df.columns)

    for label in df[label_column].unique():
        # Get all samples for the current label
        label_samples = df[df[label_column] == label]
        
        # Randomly select one-third of the samples
        num_samples = len(label_samples)
        num_to_move = num_samples // 3
        
        if num_to_move > 0:
            samples_to_move = label_samples.sample(n=num_to_move, random_state=42) # Using random_state for reproducibility
            samples_to_keep = label_samples.drop(samples_to_move.index)
            
            df_reordered = pd.concat([df_reordered, samples_to_keep])
            df_moved = pd.concat([df_moved, samples_to_move])
        else:
            df_reordered = pd.concat([df_reordered, label_samples])

    # Concatenate the kept samples and then the moved samples
    final_df = pd.concat([df_reordered, df_moved])
    
    # Save the modified DataFrame back to the original file path
    final_df.to_excel(file_path, index=False)

    print(f"Processed {file_path}")

if __name__ == "__main__":
    current_directory = os.getcwd()
    for filename in os.listdir(current_directory):
        if filename.endswith(".xlsx") and not filename.startswith("~$"):
            file_path = os.path.join(current_directory, filename)
            process_excel_file(file_path)